package com.install.core.manager;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.collections.map.ListOrderedMap;
import org.apache.commons.collections.set.ListOrderedSet;
import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.install.core.service.SpringApplicationContext;
import com.install.core.util.Pagination;
/***
 * 
 * @author pk 2013.01.16
 *
 */
public class CommManager {
	//是否有事务
	private static boolean hasUt=false;
	//处理object实例
	private static JdbcTemplate doObejct=null;
	private static TransactionStatus ts=null;
	//数据源管理类
	private static DataSourceTransactionManager dstm=null;
	//DefaultTransactionDefinition
	private static DefaultTransactionDefinition definition=null;
	public CommManager(){
		this.hasUt=false;
		initDefinition();
		initDstm();
		if(doObejct==null){
			doObejct=new JdbcTemplate(dstm.getDataSource());
		}
	}
	/***
	 * 构造函数
	 * @param hasUt 是否提供事务
	 */
	public CommManager(boolean hasUt){
		this.hasUt=hasUt;
		initDefinition();
		initDstm();
		if(doObejct==null){
			doObejct=new JdbcTemplate(dstm.getDataSource());
		}
	}
	/***
	 * 开始事务
	 */
	public void beginUt(){
		ts=dstm.getTransaction(definition);
	}
	/***
	 * 初始化initDefinition
	 */
	private static void initDefinition(){
		if(definition==null)
		definition=new DefaultTransactionDefinition();
	}
	/***
	 * 初始化initDstm
	 */
	private static void initDstm(){
		if(dstm==null)
			dstm=(DataSourceTransactionManager)SpringApplicationContext.getInstance().getBean("txManager");
		
	}
	/***
	 * 提交事务
	 */
	public  void comit(){
		if(hasUt&&dstm!=null){
			dstm.commit(ts);
		}
	}
	/***
	 * 回滚事务
	 */
	public  void rollback(){
		if(hasUt&&dstm!=null){
			dstm.rollback(ts);
		}
	}
	/***
	 * @return 返回数据库连接
	 * @throws SQLException
	 */
	public static Connection getConn() throws SQLException{
		Connection conn=dstm.getDataSource().getConnection();
		return conn;
	}
	/***
	 * 删除记录(含参数)
	 * @param sql SQL语句
	 * @param inargs 参数
	 * @return
	 */
	public int deleteWithparams(String sql,ListOrderedSet  inargs){
		Object[] params=new Object[inargs.size()];
		for (int i = 0; i < inargs.size(); i++) {
			Object ob=inargs.get(i);
			params[i]=ob;
		}
		int result=doObejct.update(sql,params);
		if(this.hasUt){
			dstm.commit(ts);
		}
		 return result;
	}
	/***
	 * 删除记录(无参)
	 * @param sql SQL语句
	 * @return
	 */
	public int delete(String sql){
		return doObejct.update(sql);
	}
	/***
	 * 更新记录(有参)
	 * @param sql
	 * @param inargs
	 * @return
	 */
	public int updateWithparams(String sql,ListOrderedSet  inargs){
		Object[] params=new Object[inargs.size()];
		for (int i = 0; i < inargs.size(); i++) {
			Object ob=inargs.get(i);
			params[i]=ob;
		}
		int result=doObejct.update(sql,params);
		if(this.hasUt){
			dstm.commit(ts);
		}
		return result;
	}
	/***
	 * 更新记录(无参)
	 * @param sql SQL语句
	 * @return
	 */
	public int update(String sql){
		return doObejct.update(sql);
	}
	/***
	 * 查询 分页
	 * @param sql
	 * @param currentPage 第几页
	 * @param numPerPage 每页多少条数据
	 * @return
	 */
	public JSONArray listByPage(String sql,int currentPage, int numPerPage ){
		JSONArray result=new JSONArray();
		Pagination p=new Pagination(sql, currentPage,numPerPage, doObejct);
		List list=p.getResultList();
		for (int i = 0; i < list.size(); i++) {
			ListOrderedMap l=(ListOrderedMap)list.get(i);
			Set set=l.entrySet();
			Iterator iter=set.iterator();
			JSONObject json=new JSONObject();
			while(iter.hasNext()){
				Entry entry = (Entry)iter.next();
				json.element(entry.getKey().toString().toUpperCase(), entry.getValue());
			}
			result.add(json);
		}
		return result;
	}
	/***
	 * 查询结果集(无参数)
	 * @param sql
	 * @return
	 */
	public JSONArray list(String sql){
		JSONArray result=new JSONArray();
		List list=doObejct.queryForList(sql);
		for (int i = 0; i < list.size(); i++) {
			ListOrderedMap l=(ListOrderedMap)list.get(i);
			Set set=l.entrySet();
			Iterator iter=set.iterator();
			JSONObject json=new JSONObject();
			while(iter.hasNext()){
				Entry entry = (Entry)iter.next();
				json.element(entry.getKey().toString().toUpperCase(), entry.getValue());
			}
			result.add(json);
		}
		return result;
	}
	/***
	 * 查询结果集(有参数，参数不能为空)
	 * @param sql
	 * @param inargs
	 * @return
	 */
	public JSONArray listWithparams(String sql,ListOrderedSet  inargs){
		Object[] params=null;
		if(inargs!=null){
			params=new Object[inargs.size()];
			for (int i = 0; i < inargs.size(); i++) {
				Object ob=inargs.get(i);
				params[i]=ob;
			}
		}
		JSONArray result=new JSONArray();
		List list=null;
		if(params==null)
			list=doObejct.queryForList(sql);
		else
			list=doObejct.queryForList(sql, params);
		for (int i = 0; i < list.size(); i++) {
			HashMap l=(HashMap)list.get(i);
			Set set=l.entrySet();
			Iterator iter=set.iterator();
			JSONObject json=new JSONObject();
			while(iter.hasNext()){
				Entry entry = (Entry)iter.next();
				json.element(entry.getKey().toString().toUpperCase(), entry.getValue());
			}
			result.add(json);
		}
		return result;
	}
	/***
	 * 执行存储过程（无参数，无返回）
	 * @param sql
	 */
	public boolean callProcedureWithOutReturnValue(String sql){
		try {
			doObejct.execute(sql);
			return true;
		} catch (DataAccessException e) {
			// TODO Auto-generated catch block
			return false;
		}
	}
	/***
	 * 执行存储过程(有参数，有结果集，结果集为1)
	 * @param sql
	 * @param inparams
	 * @param outparams
	 * @return map.get()
	 */
	public  Map callProcedureWithReturnValue(final String sql,final ListOrderedMap inparams,final ListOrderedMap outparams) { 
		Map re = (Map) doObejct.execute("{call test(?,?,?)}",new CallableStatementCallback() {
				  			public Object doInCallableStatement(CallableStatement cs)throws SQLException,DataAccessException {
				  				Set inset=inparams.entrySet();
				  				Iterator iter=inset.iterator();
				  				while(iter.hasNext()){
				  					Entry entry = (Entry)iter.next();
				  					cs.setObject(Integer.parseInt(entry.getKey().toString()), entry.getValue());
				  				}
				  				Set outset=outparams.entrySet();
				  				Iterator outiter=outset.iterator();
				  				while(outiter.hasNext()){
				  					Entry entry1 = (Entry)outiter.next();
				  					int index=Integer.parseInt(entry1.getKey().toString());
				  					int type=Integer.parseInt(entry1.getValue().toString());
				  					cs.registerOutParameter(index,type);//输出参数
				  				}
				  				
				  				cs.execute();
				  				Iterator outiter1=outset.iterator();
				  				Map result=new HashMap();
				  				while(outiter1.hasNext()){
				  					Entry entry1 = (Entry)outiter1.next();
				  					int index=Integer.parseInt(entry1.getKey().toString());
				  					int type=Integer.parseInt(entry1.getValue().toString());
				  					result.put(index, cs.getString(index));
				  				}
				  				
				  				return result;
				  			}
				  		});
		  return re;
		} 
	/***
	 * 执行存储过程(有参数，有List结果集，结果集为List游标)
	 * @param sql
	 * @param inparams
	 * @param outparams
	 * @return
	 */
	public  JSONArray callProcedureWithReturnListValue(final String sql,final ListOrderedMap inparams,final ListOrderedMap outparams) { 
		JSONArray re = (JSONArray) doObejct.execute(sql,new CallableStatementCallback() {
				  			public Object doInCallableStatement(CallableStatement cs)throws SQLException,DataAccessException {
				  				Set inset=inparams.entrySet();
				  				Iterator iter=inset.iterator();
				  				while(iter.hasNext()){
				  					Entry entry = (Entry)iter.next();
				  					cs.setObject(Integer.parseInt(entry.getKey().toString()), entry.getValue());
				  				}
				  				ResultSet rs=cs.executeQuery();
				  				JSONArray result=new JSONArray();
				  				while(rs.next()){  
				  					JSONObject obj=new JSONObject();
				  					int count=rs.getMetaData().getColumnCount();
					    	    	 for(int i=count;i>0;i--){
					    	    		 String name=rs.getMetaData().getColumnName(i);
					    	    		 obj.element(name.toUpperCase(),rs.getString(name));
					    	    	 }
					    	    	 result.add(obj);
				  				}   
				  				return result;
				  			}
				  		});
		  return re;
		} 
	public void test() { 
		  String param2Value = (String) doObejct.execute("{call test(?,?,?)}",new CallableStatementCallback() {
				  			public Object doInCallableStatement(CallableStatement cs)throws SQLException,DataAccessException {
				  				cs.setInt(1, 1);    // 输入参数
				  				cs.setInt(2, 6662);   // 输入参数
				  				cs.registerOutParameter(3,Types.VARCHAR);//输出参数
				  				cs.execute();
				  				return cs.getString(3);
				  			}
				  		});
		  System.out.println(param2Value);
		} 
	
//	private static void initTs(){
//	if(ts==null)
//		ts=dstm.getTransaction(definition);
//	
//}
//public static JdbcTemplate getInstance(){
//	if(doObejct==null){
//		initDefinition();
//		initDstm();
//		doObejct=new JdbcTemplate(dstm.getDataSource());
//	}
//	return doObejct;
//}
//public static JdbcTemplate getInstance(boolean hasUt){
//	this.hasUt=hasUt;
//	if(doObejct==null){
//		initDefinition();
//		initDstm();
//		initTs();
//		doObejct=new JdbcTemplate(dstm.getDataSource());
//	}
//	return doObejct;
//}
//	public String callProcedureWithReturnValue(final String sql) { 
//	  String param2Value = (String) doObejct.execute( 
//	     new CallableStatementCreator() { 
//	        public CallableStatement createCallableStatement(Connection con) throws SQLException { 
//	           String storedProc = sql;// 调用的sql 
//	           CallableStatement cs = con.prepareCall(storedProc); 
//	           cs.setInt(1, 1555);// 设置输入参数的值 
//	           cs.setInt(2, 2);// 设置输入参数的值 
//	           cs.registerOutParameter(3, Types.INTEGER);// 注册输出参数的类型 
//	           return cs; 
//	        } 
//	     }, new CallableStatementCallback() { 
//	         public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException { 
//	           cs.execute(); 
//	           return cs.getString(3);// 获取输出参数的值 
//	     } 
//	  }); 
//	  System.out.println(param2Value);
//	  return param2Value;
//	} 
	public static void main(String[] args) throws Exception {
		CommManager js=new CommManager(false);
		try {
			//js.getConn();
			js.beginUt();
			ListOrderedSet params=new ListOrderedSet();
			params.add(0,"2");
			//System.out.println(js.updateWithparams("update  user set uname='222ggn7ame' where uid=?",params));
			//js.callProcedureWithReturnValue("{call test(?,?,?)}");
			//js.test();
//			ListOrderedMap list=new ListOrderedMap();
//			list.put(1, 22);
//			list.put(2, 33);
//			ListOrderedMap list1=new ListOrderedMap();
//			list1.put(3, Types.INTEGER);System.out.println(list1);
//			
//			js.callProcedureWithReturnValue("{call test(?,?,?)}",list,list1);
//			Map map=js.callProcedureWithReturnValue("{call test(?,?,?)}",list,list1);
//			System.out.println(map.get(3));
			//ListOrderedMap listlist=new ListOrderedMap();
			//listlist.put(1, 2);
			//System.out.println(js.callProcedureWithReturnListValue("{call test1(?)}", listlist, listlist));
			//System.out.println(js.listByPage("select * from user",1,1));
		} catch (RuntimeException e) {
			// TODO Auto-generated catch block
			//js.rollback();
			Logger.getLogger(js.getClass()).info(e);
			Logger.getLogger(js.getClass()).error(e);
			e.printStackTrace();
		}
		//CommManager.comit();
	}
}
